/*
 * Copyright (c) 2008-2012
 * LANIT
 * All rights reserved.
 *
 * This product and related documentation are protected by copyright and
 * distributed under licenses restricting its use, copying, distribution, and
 * decompilation. No part of this product or related documentation may be
 * reproduced in any form by any means without prior written authorization of
 * LANIT and its licensors, if any.
 *
 * $Id$
 */
package ru.lanit.bpm.ipa.timesheet.dao;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import ru.lanit.bpm.ipa.timesheet.model.Employee;
import ru.lanit.commons.jdbc.JdbcOperationsExtended;

import java.sql.*;

public class JdbcEmployeeDao implements EmployeeDao {
    private static final Logger logger = LoggerFactory.getLogger(JdbcEmployeeDao.class);
    //    public static final String SQL_GET_ID = "SELECT employee_sq.nextval";
    // PXOBJCLASS = 'Lanit-FW-LanitBPMFW-Data-Employee';
    private static final String SQL_INSERT_EMPLOYEE = "" +
            "INSERT INTO LANITBPM_Employees " +
            "(PXINSNAME, PXOBJCLASS, PYLABEL, PZINSKEY, PZPVSTREAM, ID, NAME, EMAIL, PYUSERIDENTIFIER, LASTNAME, FIRSTNAME, MIDDLENAME) " +
            "VALUES " +
            "(employee_sq.nextval, ?, employee_sq.nextval, employee_sq.nextval, EMPTY_BLOB (), employee_sq.nextval, ?, ?, ?, ?, ?, ?)";
    private static final String SQL_UPDATE_PZINSKEY = "UPDATE LANITBPM_Employees set PZINSKEY = ? WHERE PXINSNAME = ?";
    private static final String SQL_SELECT_BY_NAME = "" +
            "SELECT " +
            "PXINSNAME, PXOBJCLASS, PYLABEL, PZINSKEY, PZPVSTREAM, ID, NAME, EMAIL, PYUSERIDENTIFIER, LASTNAME, FIRSTNAME, MIDDLENAME " +
            "FROM " +
            "LANITBPM_Employees " +
            "WHERE " +
            "NAME = ?";
    //Req Nested Entity
    private static final String SQL_SELECT_OD_ID_BY_OD_NAME = "SELECT ID FROM LANITBPM_Projects WHERE NAME = ?";
    private static final String SQL_SELECT_ROLE_ID_BY_ROLE_NAME = "SELECT ID FROM LANITBPM_Roles WHERE NAME = ?";
    private static final String SQL_INSERT_OD = "" +
            "INSERT INTO LANITBPM_EmployeeToProject " +
            "(PXINSNAME, PXOBJCLASS, PYLABEL, PZINSKEY, PZPVSTREAM, ID, EmployeeId, ProjectId, RoleId) " +
            "VALUES " +
            "(od_sq.nextval, ?, od_sq.nextval, od_sq.nextval, EMPTY_BLOB (), od_sq.nextval, ?, ?, ?)";
    private static final String SQL_UPDATE_PZINSKEY_OD = "UPDATE LANITBPM_EmployeeToProject set PZINSKEY = ? WHERE PXINSNAME = ?";
    private static final String SQL_SELECT_EMPLOYEE_ID_BY_NAME = "" +
            "SELECT " +
            "ID " +
            "FROM " +
            "LANITBPM_Employees " +
            "WHERE " +
            "NAME = ?";
    private final JdbcOperationsExtended jdbcOperations;

    public JdbcEmployeeDao(JdbcOperationsExtended jdbcOperations) {
        this.jdbcOperations = jdbcOperations;
    }

    public void add(final Employee employee) throws DataAccessException {
        logger.debug("add {}", employee);
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        String pxObjClass = "Lanit-FW-LanitBPMFW-Data-Employee";
        employee.setPxObjClass(pxObjClass);

        jdbcOperations.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(SQL_INSERT_EMPLOYEE, new String[]{"ID"});
                preparedStatement.setString(1, employee.getPxObjClass());
                preparedStatement.setString(2, employee.getName());
                preparedStatement.setString(3, employee.getEmail());
                preparedStatement.setString(4, employee.getPyUserIdentifier());
                preparedStatement.setString(5, employee.getLastName());
                preparedStatement.setString(6, employee.getFirstName());
                preparedStatement.setString(7, employee.getMiddleName());
                return preparedStatement;
            }
        }, generatedKeyHolder);

        employee.setId(generatedKeyHolder.getKey().longValue());
        employee.setPxInsName(generatedKeyHolder.getKey().toString());
        employee.setPyLabel(generatedKeyHolder.getKey().toString());

        employee.setPzInsKey(employee.getPxObjClass() + " " + employee.getId());

        jdbcOperations.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(SQL_UPDATE_PZINSKEY);
                preparedStatement.setString(1, employee.getPzInsKey());
                preparedStatement.setLong(2, employee.getId());
                return preparedStatement;
            }
        });
    }

    public void addEmployeeToOD(String employeeName, final String operationalDirectionName, String roleName) throws DataAccessException {
        logger.debug("addEmployeeToOD({}, {}, {})", new Object[]{employeeName, operationalDirectionName, roleName});

        final long employeeId = jdbcOperations.queryForLong(SQL_SELECT_EMPLOYEE_ID_BY_NAME, new Object[]{employeeName}, new int[]{Types.LONGVARCHAR});
        final long operationalDirectionId;
        try {
            operationalDirectionId = jdbcOperations.queryForLong(SQL_SELECT_OD_ID_BY_OD_NAME,
                    new Object[]{operationalDirectionName}, new int[]{Types.LONGVARCHAR});
        } catch (EmptyResultDataAccessException e) {
            System.out.println("operationalDirectionName = " + operationalDirectionName);
            throw e;
        }
        final long roleId = jdbcOperations.queryForLong(SQL_SELECT_ROLE_ID_BY_ROLE_NAME, new Object[]{roleName}, new int[]{Types.LONGVARCHAR});

        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();

        final String pxObjClass = "Lanit-FW-LanitBPMFW-Data-EmployeeToProject";

        try {
            jdbcOperations.update(new PreparedStatementCreator() {

                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement preparedStatement = connection.prepareStatement(SQL_INSERT_OD, new String[]{"ID"});
                    preparedStatement.setString(1, pxObjClass);
                    preparedStatement.setLong(2, employeeId);
                    preparedStatement.setLong(3, operationalDirectionId);
                    preparedStatement.setLong(4, roleId);
                    return preparedStatement;
                }
            }, generatedKeyHolder);
        } catch (DuplicateKeyException e) {
            logger.debug("addEmployeeToOD: {}", e.getMessage());
            return;
        }
        final long employeeToOdId = generatedKeyHolder.getKey().longValue();

        final String pzInsKey = pxObjClass + " " + employeeToOdId;

        jdbcOperations.update(new PreparedStatementCreator() {

            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(SQL_UPDATE_PZINSKEY_OD);
                preparedStatement.setString(1, pzInsKey);
                preparedStatement.setLong(2, employeeToOdId);
                return preparedStatement;
            }
        });
    }

    public Employee findByName(String name) throws DataAccessException {
        logger.debug("findByName({})", name);
        Employee employee = null;
        try {
            employee = jdbcOperations.queryForObject(SQL_SELECT_BY_NAME, new Object[]{name}, new int[]{Types.LONGVARCHAR},
                    new EmployeeRowMapper());
        } catch (EmptyResultDataAccessException ignored) {
        }
        logger.info("Found {}", employee);
        return employee;
    }

    // ===================================================================================================================
    // = Implementation
    // ===================================================================================================================
    private static class EmployeeRowMapper implements RowMapper<Employee> {

        public Employee mapRow(ResultSet resultSet, int i) throws SQLException {
            String pxInsName = resultSet.getString(1); // ID
            String pxObjClass = resultSet.getString(2); // ClassName
            String pyLabel = resultSet.getString(3); // ID
            String pzInsKey = resultSet.getString(4); // Class + _ + ID
            Blob pzPvStream = resultSet.getBlob(5); // (BLOB)

            // Semantic fields
            Long id = resultSet.getLong(6);
            String name = resultSet.getString(7);
            String email = resultSet.getString(8);
            String pyUserIdentifier = resultSet.getString(9);
            String lastName = resultSet.getString(10);
            String firstName = resultSet.getString(11);
            String middleName = resultSet.getString(12);
            Employee employee = new Employee(pxInsName, pxObjClass, pyLabel, pzInsKey, pzPvStream, id, name, email,
                    pyUserIdentifier, lastName, firstName, middleName);
            logger.debug("Found {}", employee);
            return employee;
        }
    }

    private void checkUniqueEmployeeToOD(long employeeId, long operationalDirectionId, long roleId) throws DataAccessException {
        final String SQL_SELECT_EMPLOYEE_TO_OD = "SELECT ID FROM LANITBPM_EmployeeToProject WHERE EmployeeId = ? and ProjectId = ? and RoleId = ?";
    }
}

